package com.rec.dao;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import oracle.jdbc.OracleTypes;
import utiles.AppProperties;
import utiles.ConexionPool;


import com.ctb.dto.docAnuladoDTO;
import com.rec.dto.AreaDTO;
import com.rec.dto.ArqueoMovimientosDTO;
import com.rec.dto.ArqueoPagosDTO;
import com.rec.dto.ContratoDTO;
import com.rec.dto.GiradorDTO;
import com.rec.dto.ListaPrecioDTO;
import com.rec.dto.PagoEncDTO;
import com.rec.dto.ServicioDTO;
import com.rec.dto.ProductoDTO;
import com.rec.dto.TotalesItemDTO;
import com.rec.dto.docDetDTO;
import com.rec.dto.historialPacDTO;
import com.rec.vo.DocDetVOTH;
import com.rec.vo.DocEncVO;
import com.rec.vo.DocEncVOTh;
import com.rec.vo.ProductoVO;
import com.rec.vo.listaPrecioVO;
import com.rec.vo.pagoDetCajaVO;
import com.rec.vo.pagoDetIngVO;
import com.rec.vo.pagoDetVO;
//import com.rec.vo.docEncDTO;
import com.rec.dto.docEncDTO;  
import com.rec.vo.pagoEncVO;
import com.rec.dto.BoletaDTO;
import com.rec.vo.DocDetVO;
import com.social.dto.GratuidadDetDTO;


public class ServiciosDAO {
	private static Logger log = Logger.getLogger(ServiciosDAO.class);
	String pkgVar = (AppProperties.getProperty("aplicacion", "PACKAGE"));

	public List<ServicioDTO> getTiposServicios(String idArea) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();	
		List<ServicioDTO> listTP = new ArrayList<ServicioDTO>();
		try {
			String query = "{ call "+pkgVar+".Pkg_rec_servicios.getTiposServicios(?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, idArea);			
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);
			while (rs.next()) {
				ServicioDTO tpDTO = new ServicioDTO();
				tpDTO.setId_tipo_producto(rs.getString(1));
				tpDTO.setStr_descripcion(rs.getString(2));			
				listTP.add(tpDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error ServiciosDAO.getTiposServicios SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			conn.close();
			conn = null;
		}
		return listTP;
	}
	
	
	public List<ServicioDTO> getServicios(String idContra, String idArea, String idTipo, String strTexto, String rutPac) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();	
		List<ServicioDTO> listTP = new ArrayList<ServicioDTO>();
		try {
			String query = "{ call "+pkgVar+".Pkg_rec_servicios.getServicios(?,?,?,?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, idContra);
			stmt.setString(2, idArea);
			stmt.setString(3, idTipo);
			stmt.setString(4, strTexto);
			stmt.setString(5, rutPac);
			stmt.registerOutParameter(6, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(6);
			while (rs.next()) {
				ServicioDTO tpDTO = new ServicioDTO();
				tpDTO.setId_producto(rs.getString(1));
				tpDTO.setId_tipo_producto(rs.getString(2));
				tpDTO.setStr_descripcion(rs.getString(3));
				tpDTO.setStr_codigo(rs.getString(4));
				tpDTO.setPreciolista(rs.getString(5));
				tpDTO.setGratuidad(rs.getString(6));
				listTP.add(tpDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error ServiciosDAO.getServicios SQLException: " +ex.getMessage());
			System.out.println("Error ServiciosDAO.getServicios SQLException: "+ ex.getMessage());
		} finally {
			conn.close();
			conn = null;
		}
		return listTP;
	}
	
	public List<ProductoDTO> getListaPrecioByIDPrIDCont(String IDContrato, String IDProducto) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();		    
		List<ProductoDTO> listProducto = new ArrayList<ProductoDTO>();
		try {
			String query = "{ call "+pkgVar+".Pkg_rec_servicios.getListaPreciobyIDPr_IDCont(?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, IDContrato);			
			stmt.setString(2, IDProducto);
			stmt.registerOutParameter(3, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(3);
			while (rs.next()) {
				ProductoDTO proDTO = new ProductoDTO();
				proDTO.setId_producto(rs.getString(1));
				proDTO.setId_tipo_producto(rs.getString(2));
				proDTO.setStr_descripcion(rs.getString(3));
				proDTO.setStr_codigo(rs.getString(4));
				proDTO.setPreciolista(rs.getString(5));
				listProducto.add(proDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error ServiciosDAO.getListaPrecioByIDPrIDCont SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			conn.close();
			conn = null;
		}
		return listProducto;
	}
	
	public List<historialPacDTO> getDocumentos(String rutPAC, String idArea, String fecha, String estado) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();		    
		List<historialPacDTO> listResultado = new ArrayList<historialPacDTO>();
		try {                                            
			String query = "{ call "+pkgVar+".Pkg_rec_servicios.getDocumentos(?,?,?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, rutPAC);						
			stmt.setString(2, idArea);
			stmt.setString(3, fecha);
			stmt.setString(4, estado);
			stmt.registerOutParameter(5, OracleTypes.CURSOR);
			stmt.execute();			
			ResultSet rs = (ResultSet)stmt.getObject(5);
			while (rs.next()) {
				historialPacDTO histDTO = new historialPacDTO();	
				histDTO.setFecVenta(rs.getString(1));
				histDTO.setFolioVenta(rs.getString(2));
				histDTO.setAreaDescripcion(rs.getString(3));
				histDTO.setTotalPrestacion(rs.getString(4));
				histDTO.setTotalDonacion(rs.getString(5));
				histDTO.setCanTotal(rs.getString(6));
				histDTO.setEstado(rs.getString(7));
				if (rs.getString(7).equals("PENDIENTE")){
					histDTO.setFormaPago(rs.getString(10));
					histDTO.setContrato(rs.getString(11));
				}else{
					histDTO.setFormaPago(rs.getString(8));
					histDTO.setContrato(rs.getString(9));
				}
				histDTO.setIdUser(rs.getString(12));
				histDTO.setPrmAnular(rs.getString(13));
				histDTO.setUsuario(rs.getString(14));
				histDTO.setUsuariorec(rs.getString(15));
				histDTO.setDescPeso(rs.getString(16));
				histDTO.setRutCompleto(rs.getString(17));
				histDTO.setNombre(rs.getString(18));
				histDTO.setIdFormaPago(rs.getString(19));
				histDTO.setIdContrato(rs.getString(20));
				if(rs.getString(21).equals("S")){
					histDTO.setStrPagado("PAGADO");	
				}else{
					histDTO.setStrPagado("SIN PAGAR");
				}
				histDTO.setRut(rs.getString(22));
				histDTO.setRutDv(rs.getString(23));
				histDTO.setIdPago(rs.getString(24));
				histDTO.setIdTipoContrato(rs.getString(25));
				histDTO.setRutContrato(rs.getString(26));
				histDTO.setSumaGratuidad(rs.getString(27));
				histDTO.setPorcentajeGratuidad(rs.getString(28));
				listResultado.add(histDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error(" Error ServicioDAO.getDocumentos SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			conn.close();
			conn = null;
		}
		return listResultado;
		
	}
	
	 public List<docEncDTO> insertDocEncNewCaja(DocEncVO docEncVO) throws SQLException, IOException{
			List<docEncDTO> listIDMovTotal = new ArrayList<docEncDTO>();
			Connection conn = ConexionPool.getConnectionPool();
					try {
						String query = "{ call "+pkgVar+".Pkg_rec_servicios.setInsDocEnc(?,?,?,?,?,?,?,?,?,?,?) }";
						CallableStatement  stmt = conn.prepareCall(query);
						stmt.setString(1, docEncVO.getFolioVenta());
						stmt.setString(2, docEncVO.getIdArea());		                
						stmt.setString(3, docEncVO.getIdPaciente());
						stmt.setString(4, docEncVO.getIdFormaPago());
						stmt.setString(5, docEncVO.getIdContrato());
						stmt.setString(6, docEncVO.getCantTotal());
						stmt.setString(7, docEncVO.getTotPrestacion());
						stmt.setString(8, docEncVO.getTotalDonacion());				
						stmt.setString(9, docEncVO.getDescuento());
						stmt.setString(10, docEncVO.getIdUsuario());								
						stmt.registerOutParameter(11, OracleTypes.CURSOR);
						stmt.execute();
						ResultSet rs = (ResultSet)stmt.getObject(11);
						while (rs.next()) {
							docEncDTO movTotal = new docEncDTO();
							movTotal.setFolioVenta(rs.getString(1));
							movTotal.setFecVenta(rs.getString(2));
							movTotal.setEstado(rs.getString(3));;
							listIDMovTotal.add(movTotal);					
						}
						stmt.close();
					} catch (SQLException ex) {  
						log.error("Error Servicios dao Pkg_rec_servicios.insertDocEncNew SQLException: " +ex.getMessage());
						System.out.println(ex.getMessage());				
					} finally {
						conn.close();
						conn = null;
					}						
					
				return listIDMovTotal;
		}	
	 
	 public List<docEncDTO> insertDocEncNewTH(DocEncVOTh docEncVO) throws SQLException, IOException{
			List<docEncDTO> listIDMovTotal = new ArrayList<docEncDTO>();
			Connection conn = ConexionPool.getConnectionPool();
					try {
						String query = "{ call "+pkgVar+".Pkg_rec_servicios.setInsDocEnc(?,?,?,?,?,?,?,?,?,?,?) }";
						CallableStatement  stmt = conn.prepareCall(query);
						stmt.setString(1, docEncVO.getFolioVenta());
						stmt.setString(2, docEncVO.getIdArea());		                
						stmt.setString(3, docEncVO.getIdPaciente());
						stmt.setString(4, docEncVO.getIdFormaPago());
						stmt.setString(5, docEncVO.getIdContrato());
						stmt.setString(6, docEncVO.getCantTotal());
						stmt.setString(7, docEncVO.getTotPrestacion());
						stmt.setString(8, docEncVO.getTotalDonacion());				
						stmt.setString(9, docEncVO.getDescuento());
						stmt.setString(10, docEncVO.getIdUsuario());								
						stmt.registerOutParameter(11, OracleTypes.CURSOR);
						stmt.execute();
						ResultSet rs = (ResultSet)stmt.getObject(11);
						while (rs.next()) {
							docEncDTO movTotal = new docEncDTO();
							movTotal.setFolioVenta(rs.getString(1));
							movTotal.setFecVenta(rs.getString(2));
							movTotal.setEstado(rs.getString(3));;
							listIDMovTotal.add(movTotal);					
						}
						stmt.close();
					} catch (SQLException ex) {  
						log.error("Error Servicios dao Pkg_rec_servicios.insertDocEncNew SQLException: " +ex.getMessage());
						System.out.println(ex.getMessage());				
					} finally {
						conn.close();
						conn = null;
					}						
					
				return listIDMovTotal;
		}	
	 
		public List<docAnuladoDTO> setAnularDocByFolio(String folio, String iduser) throws SQLException, IOException{
			Connection conn = ConexionPool.getConnectionPool();
			List<docAnuladoDTO> listResultado = new ArrayList<docAnuladoDTO>();
			try {                                            
				String query = "{ call "+pkgVar+".Pkg_rec_servicios.setAnularDocByFolio(?,?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, folio);
				stmt.setString(2, iduser);
				stmt.registerOutParameter(3, OracleTypes.CURSOR);
				stmt.execute();			
				ResultSet rs = (ResultSet)stmt.getObject(3);
				while (rs.next()) {
					docAnuladoDTO docnuloDTO = new docAnuladoDTO();
					docnuloDTO.setUSUARIO(rs.getString(1));
					docnuloDTO.setFOLIOVENTA(rs.getString(2));
					docnuloDTO.setFECDIG(rs.getString(3));
					listResultado.add(docnuloDTO);
				}
				rs.close();
				stmt.close();
			} catch (SQLException ex) {  
				log.error("Error Servicios dao Pkg_rec_servicio.setAnularDocByFolio SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());	
			} finally {
				conn.close();
				conn = null;
			}
			return listResultado;		
		}	
		
		 public List<PagoEncDTO> getValidaPagos(DocEncVO docencVO) throws SQLException, IOException{
			 List<PagoEncDTO> listValidaFolio = new ArrayList<PagoEncDTO>();
		 	 Connection conn = ConexionPool.getConnectionPool();	 				
		 	 try {						
		 		 String query = "{ call "+pkgVar+".Pkg_rec_servicios.getValidaPagos(?,?,?,?) }";
				 CallableStatement  stmt = conn.prepareCall(query);
				 stmt.setString(1, docencVO.getFolioVenta());
				 stmt.setString(2, docencVO.getIdContrato());
				 stmt.setString(3, docencVO.getIdArea());
				 stmt.registerOutParameter(4, OracleTypes.CURSOR);
				 stmt.execute();
				 //System.out.println("Folio:"+docencVO.getFolioVenta()+"Pago"+docencVO.getIdArea()+"pp");
				 ResultSet rs = (ResultSet)stmt.getObject(4);
				 while (rs.next()) {
					 PagoEncDTO movTotal = new PagoEncDTO();
					 movTotal.setIdPago(rs.getString(1));
					 System.out.println(" Folio ya recaudado:"+docencVO.getFolioVenta()+"Id Pago"+docencVO.getIdArea());
					 listValidaFolio.add(movTotal);					
				 	}			
				 stmt.close();
				} catch (SQLException ex) {  
					log.error("Error Servicios dao Pkg_rec_servicios.getValidaPagos SQLException: " +ex.getMessage());
					System.out.println(ex.getMessage());					
				} finally {
					conn.close();
					conn = null;
				}			
			return listValidaFolio;
		}		
		 
		 public List<PagoEncDTO> setPagoEnc(pagoEncVO pagoEnc) throws SQLException, IOException{
				List<PagoEncDTO> listIDMovTotal = new ArrayList<PagoEncDTO>();
				Connection conn = ConexionPool.getConnectionPool();
						try {
							String query = "{ call "+pkgVar+".Pkg_rec_servicios.setPagoEnc(?,?,?,?,?) }";
							CallableStatement  stmt = conn.prepareCall(query);
							stmt.setString(1, pagoEnc.getIdPago());
							stmt.setString(2, pagoEnc.getTotal());		                
							stmt.setString(3, pagoEnc.getFechaPago());
							stmt.setString(4, pagoEnc.getIdUsuario());							
							stmt.registerOutParameter(5, OracleTypes.CURSOR);
							stmt.execute();
							ResultSet rs = (ResultSet)stmt.getObject(5);
							while (rs.next()) {
								PagoEncDTO movTotal = new PagoEncDTO();
								movTotal.setIdPago(rs.getString(1));
								movTotal.setFechaPago(rs.getString(2));
								movTotal.setFechaDigitacion(rs.getString(3));
								movTotal.setEstado(rs.getString(4));
								listIDMovTotal.add(movTotal);					
							}
							stmt.close();
						} catch (SQLException ex) {  
							log.error(" Error en ServiciosDAO Pkg_rec_servicios.setPagoEnc SQLException: " +ex.getMessage());//horaActual+
							System.out.println(ex.getMessage());				
						} finally {
							conn.close();
							conn = null;
						}						
						
					return listIDMovTotal;
			}	
		 
		 public int deletePagoDetalle(pagoDetVO pdVO) throws SQLException, IOException{
				int idMovimiento = 0;
				Connection conn = ConexionPool.getConnectionPool(); 
						try {						
							String query = "{ call "+pkgVar+".Pkg_rec_servicios.setPagoDetDel(?) }";
							CallableStatement  stmt = conn.prepareCall(query);
							stmt.setString(1, pdVO.getIdPagoDet());
							stmt.execute();
							stmt.close();
						} catch (SQLException ex) {  
							log.error(" Error ServiciosDAO.deletePagoDetalle SQLException: " +ex.getMessage());//horaActual+
							System.out.println(ex.getMessage());				
						} finally {
							conn.close();
							conn = null;
						}			
					return idMovimiento;
			}		
		 
		 public int setPagoDetalleIng(pagoDetIngVO pdVO) throws SQLException, IOException{
				int idMovimiento = 0;
				Connection conn = ConexionPool.getConnectionPool();  
						try {						
							String query = "{ call "+pkgVar+".Pkg_rec_servicios.setPagoDet(?,?,?,?,?,?,?,?,?,?,?) }";
							CallableStatement  stmt = conn.prepareCall(query);
							stmt.setString(1, pdVO.getIdPagoDet());
							stmt.setString(2, pdVO.getIdContrato());
							stmt.setString(3, pdVO.getIdPago());
							stmt.setString(4, pdVO.getIdCheque());
							stmt.setString(5, pdVO.getIdFormaPago());
							stmt.setString(6, pdVO.getMonto());
							stmt.setString(7, pdVO.getIdBanco());
							stmt.setString(8, pdVO.getBanco());
							stmt.setString(9, pdVO.getFecVenc2());
							stmt.setString(10, pdVO.getSerieChq());
							stmt.setString(11, pdVO.getRutGirador());
							stmt.execute();
							stmt.close();
						} catch (SQLException ex) {  
							log.error(" Error ServiciosDAO.setPagoDetalle SQLException: " +ex.getMessage());//horaActual+
							System.out.println(ex.getMessage());				
						} finally {
							conn.close();
							conn = null;
						}			
					return idMovimiento;
			}		
		 
		 public int setPagoDetalleCaja(pagoDetCajaVO pdVO) throws SQLException, IOException{
				int idMovimiento = 0;
				Connection conn = ConexionPool.getConnectionPool();  
						try {						
							String query = "{ call "+pkgVar+".Pkg_rec_servicios.setPagoDet(?,?,?,?,?,?,?,?,?,?,?) }";
							CallableStatement  stmt = conn.prepareCall(query);
							stmt.setString(1, pdVO.getIdPagoDet());
							stmt.setString(2, pdVO.getIdContrato());
							stmt.setString(3, pdVO.getIdPago());
							stmt.setString(4, pdVO.getIdCheque());
							stmt.setString(5, pdVO.getIdFormaPago());
							stmt.setString(6, pdVO.getMonto());
							stmt.setString(7, pdVO.getIdBanco());
							stmt.setString(8, pdVO.getBanco());
							stmt.setString(9, pdVO.getFecVenc2());
							stmt.setString(10, pdVO.getSerieChq());
							stmt.setString(11, pdVO.getRutGirador());
							stmt.execute();
							stmt.close();
						} catch (SQLException ex) {  
							log.error(" Error ServiciosDAO.setPagoDetalle SQLException: " +ex.getMessage());//horaActual+
							System.out.println(ex.getMessage());				
						} finally {
							conn.close();
							conn = null;
						}			
					return idMovimiento;
			}		 
		 
			public List<BoletaDTO> setPagoDetServicio(String idPago) throws SQLException, IOException{
				Connection conn = ConexionPool.getConnectionPool();
				List<BoletaDTO> listCierre = new ArrayList<BoletaDTO>();
				try {                                            
					String query = "{ call "+pkgVar+".Pkg_rec_servicios.setPagoDetServicio(?,?) }";
					CallableStatement  stmt = conn.prepareCall(query);
					stmt.setString(1, idPago);
					stmt.registerOutParameter(2, OracleTypes.CURSOR);
					stmt.execute();
					ResultSet rs = (ResultSet)stmt.getObject(2);
					while (rs.next()) {
						BoletaDTO totItem = new BoletaDTO();
						totItem.setCliente(rs.getString(1));
						totItem.setRut(rs.getString(2));
						totItem.setFecha(rs.getString(3));
						totItem.setFolio(rs.getString(4));
						totItem.setDcporc(rs.getString(5));
						totItem.setDescpeso(rs.getString(6));
						totItem.setTotpagar(rs.getString(7));
						totItem.setServicio(rs.getString(8));
						totItem.setPrecio(rs.getString(9));
						listCierre.add(totItem);
					}
					rs.close();
					stmt.close();
				} catch (SQLException ex) {  
					log.error(" Error ServiciosDAO.setPagoDetServicio SQLException: " +ex.getMessage());//horaActual+
					System.out.println(ex.getMessage());
				} finally {
					conn.close();
					conn = null;
				}
				return listCierre;
			}		 
			
			 public int setPagoUnion(DocEncVO docencVO) throws SQLException, IOException{
					int idMovimiento = 0;
					Connection conn = ConexionPool.getConnectionPool(); 
							try {						
								String query = "{ call "+pkgVar+".Pkg_rec_servicios.setPagoUnion(?,?,?) }";
								CallableStatement  stmt = conn.prepareCall(query);
								stmt.setString(1, docencVO.getFolioVenta());
								stmt.setString(2, docencVO.getIdContrato());
								stmt.setString(3, docencVO.getIdArea());
								stmt.execute();
								stmt.close();
							} catch (SQLException ex) {  
								log.error(" Error ServciosDAO.setPagoUnion SQLException: " +ex.getMessage());//horaActual+
								System.out.println(ex.getMessage());				
							} finally {
								conn.close();
								conn = null;
							}			
						return idMovimiento;
				}			
			 
				public List<docAnuladoDTO> setBorrarPago(String folio, String iduser) throws SQLException, IOException{
					Connection conn = ConexionPool.getConnectionPool();
					List<docAnuladoDTO> listResultado = new ArrayList<docAnuladoDTO>();
					try {                                            
						String query = "{ call "+pkgVar+".Pkg_rec_servicios.setBorrarPago(?,?,?) }"; 	       
						CallableStatement  stmt = conn.prepareCall(query);
						stmt.setString(1, folio);
						stmt.setString(2, iduser);
						stmt.registerOutParameter(3, OracleTypes.CURSOR);
						stmt.execute();			
						ResultSet rs = (ResultSet)stmt.getObject(3);
						while (rs.next()) {
							docAnuladoDTO docnuloDTO = new docAnuladoDTO();
							docnuloDTO.setUSUARIO(rs.getString(1));
							docnuloDTO.setFOLIOVENTA(rs.getString(2));
							docnuloDTO.setFECDIG(rs.getString(3));
							listResultado.add(docnuloDTO);
						}
						rs.close();
						stmt.close();
					} catch (SQLException ex) {  
						log.error(" Error ServiciosDAO.setBorrarPago SQLException: " +ex.getMessage());//horaActual+
						System.out.println(ex.getMessage());
					} finally {
						conn.close();
						conn = null;
					}
					return listResultado;		
				}
				
		// mantenedor servicios y lista precios ------------------------------------------------
				
				
				public List<ProductoDTO> getTipoProdByIdArea(String idArea) throws SQLException, IOException{
					Connection conn = ConexionPool.getConnectionPool();	    
					List<ProductoDTO> listTP = new ArrayList<ProductoDTO>();
					try {
						String query = "{ call "+pkgVar+".Pkg_rec_servicios.getTipoProdByIdArea(?,?) }"; 	       
						CallableStatement  stmt = conn.prepareCall(query);
						stmt.setString(1, idArea);			
						stmt.registerOutParameter(2, OracleTypes.CURSOR);
						stmt.execute();
						ResultSet rs = (ResultSet)stmt.getObject(2);
						while (rs.next()) {
							ProductoDTO tpDTO = new ProductoDTO();
							tpDTO.setId_tipo_producto(rs.getString(1));
							tpDTO.setStr_descripcion(rs.getString(2));			
							listTP.add(tpDTO);
						}
						rs.close();
						stmt.close();
					} catch (SQLException ex) {  
						log.error(" Error ServiciosDAO.getTipoProdByIdArea SQLException: " +ex.getMessage());//horaActual+
						System.out.println(ex.getMessage());
					} finally {
						conn.close();
						conn = null;
					}
					return listTP;
				}
				
			/*Estaba en ProductoDAO*/	
				public List<ProductoDTO> getProdByServicio(String Servicio, String vigente, String idArea) throws SQLException, IOException{
				Connection conn = ConexionPool.getConnectionPool();		    
					List<ProductoDTO> listProducto = new ArrayList<ProductoDTO>();
					try {
						String query = "{ call "+pkgVar+".Pkg_rec_servicios.getProdByServicio(?,?,?,?) }"; 	       
						CallableStatement  stmt = conn.prepareCall(query);
						stmt.setString(1, Servicio);
						stmt.setString(2, vigente);
						stmt.setString(3, idArea);
						stmt.registerOutParameter(4, OracleTypes.CURSOR);
						stmt.execute();
						ResultSet rs = (ResultSet)stmt.getObject(4);
						while (rs.next()) {
							ProductoDTO proDTO = new ProductoDTO();
							proDTO.setId_producto(rs.getString(1));
							proDTO.setId_tipo_producto(rs.getString(2));
							proDTO.setStr_descripcion(rs.getString(3));
							proDTO.setStr_codigo(rs.getString(4));
							listProducto.add(proDTO);
						}
						rs.close();
						stmt.close();
					} catch (SQLException ex) {  
						log.error("Error serviciosDAO.getProdByServicio SQLException: " +ex.getMessage());
						System.out.println(ex.getMessage());
					} finally {
						conn.close();
						conn = null;
					}
					return listProducto;
				}					
				
			public List<ProductoDTO> setProdDelUpIns(ProductoVO proVO) throws SQLException, IOException{
				Connection conn = ConexionPool.getConnectionPool();		    
				List<ProductoDTO> listProducto = new ArrayList<ProductoDTO>();
				try {
					String query = "{ call "+pkgVar+".Pkg_rec_servicios.setProdDelUpIns(?,?,?,?,?,?) }"; 	       
					CallableStatement  stmt = conn.prepareCall(query);
					stmt.setString(1, proVO.getIdProducto());
					stmt.setString(2, proVO.getIdTipoProducto());
					stmt.setString(3, proVO.getDescripcion());
					stmt.setString(4, proVO.getCodigo());
					stmt.setString(5, proVO.getTipo());
					stmt.registerOutParameter(6, OracleTypes.CURSOR);
					stmt.execute();
					ResultSet rs = (ResultSet)stmt.getObject(6);
					while (rs.next()) {
						ProductoDTO proDTO = new ProductoDTO();
						proDTO.setId_producto(rs.getString(1));
						proDTO.setStr_descripcion(rs.getString(2));
						listProducto.add(proDTO);
					}
					rs.close();
					stmt.close();
				} catch (SQLException ex) {  
					log.error("Error ServiciosDAO.setProdDelUpIns SQLException: " +ex.getMessage());
					System.out.println(ex.getMessage());
				} finally {
					conn.close();
					conn = null;
				}
				return listProducto;
			}
			
			public List<ContratoDTO> getAllTipoContrato() throws SQLException, IOException{
				Connection conn = ConexionPool.getConnectionPool();				    
				List<ContratoDTO> listContrato = new ArrayList<ContratoDTO>();
				try {
					String query = "{ call "+pkgVar+".Pkg_re_servicios.getAllTipoContrato(?) }"; 	       
					CallableStatement  stmt = conn.prepareCall(query);
					stmt.registerOutParameter(1, OracleTypes.CURSOR);
					stmt.execute();
					ResultSet rs = (ResultSet)stmt.getObject(1);
					while (rs.next()) {
						ContratoDTO contratoDTO = new ContratoDTO();
						contratoDTO.setId_contrato(rs.getString(1));
						contratoDTO.setStr_descripcion(rs.getString(2));			
						listContrato.add(contratoDTO);
					}
					rs.close();
					stmt.close();
				} catch (SQLException ex) {  
					log.error("Error ServiciosDAO.getAllTipoContrato SQLException: " +ex.getMessage());
					System.out.println(ex.getMessage());
				} finally {
					conn.close();
					conn = null;
				}
				return listContrato;
			}		
			
			public List<ContratoDTO> getByTipoContra(String tipoContrato) throws SQLException, IOException{
				Connection conn = ConexionPool.getConnectionPool();	    
				List<ContratoDTO> listContrato = new ArrayList<ContratoDTO>();
				try {
					String query = "{ call "+pkgVar+".Pkg_rec_servicios.getByTipoContra(?,?) }"; 	       
					CallableStatement  stmt = conn.prepareCall(query);
					stmt.setString(1, tipoContrato);			
					stmt.registerOutParameter(2, OracleTypes.CURSOR);
					stmt.execute();
					ResultSet rs = (ResultSet)stmt.getObject(2);
					while (rs.next()) {
						ContratoDTO contratoDTO = new ContratoDTO();
						contratoDTO.setId_contrato(rs.getString(1));
						contratoDTO.setStr_descripcion(rs.getString(5));			
						listContrato.add(contratoDTO);
					}
					rs.close();
					stmt.close();
				} catch (SQLException ex) {  
					log.error("Error ContratoDAO.getByTipoContra SQLException: " +ex.getMessage());
					System.out.println(ex.getMessage());
				} finally {
					conn.close();
					conn = null;
				}
				return listContrato;
			}			
			
			public List<ListaPrecioDTO> getLPbyProducto(String contrato, String idarea, String idprod) throws SQLException, IOException{
				Connection conn = ConexionPool.getConnectionPool();			    
				List<ListaPrecioDTO> listLP = new ArrayList<ListaPrecioDTO>();
				try {
					String query = "{ call "+pkgVar+".Pkg_rec_servicios.getByProducto(?,?,?,?) }"; 	       
					CallableStatement  stmt = conn.prepareCall(query);
					stmt.setString(1, contrato);
					stmt.setString(2, idarea);
					stmt.setString(3, idprod);
					stmt.registerOutParameter(4, OracleTypes.CURSOR);
					stmt.execute();
					ResultSet rs = (ResultSet)stmt.getObject(4);
					while (rs.next()) {
						ListaPrecioDTO lpDTO = new ListaPrecioDTO();
						lpDTO.setId_lista_precio(rs.getString(1));
						lpDTO.setId_producto(rs.getString(2));
						lpDTO.setServicio(rs.getString(3));
						lpDTO.setFec_ingreso_vigencia(rs.getString(4));
						lpDTO.setNum_precio(rs.getString(5));
						lpDTO.setCodigo(rs.getString(6));
						listLP.add(lpDTO);
					}
					rs.close();
					stmt.close();
				} catch (SQLException ex) {  
					log.error("Error ServiciosDAO.getLPbyProducto SQLException: " +ex.getMessage());
					System.out.println(ex.getMessage());
				} finally {
					conn.close();
					conn = null;
				}
				return listLP;
			}
			
			public List<ListaPrecioDTO> getLPbyContrato(String contrato, String idarea, String vigente, String servicio) throws SQLException, IOException{
				Connection conn = ConexionPool.getConnectionPool();			    
				List<ListaPrecioDTO> listLP = new ArrayList<ListaPrecioDTO>();
				try {
					String query = "{ call "+pkgVar+".Pkg_rec_servicios.getByContrato(?,?,?,?,?) }"; 	       
					CallableStatement  stmt = conn.prepareCall(query);
					stmt.setString(1, contrato);
					stmt.setString(2, idarea);
					stmt.setString(3, vigente);
					stmt.setString(4, servicio);
					stmt.registerOutParameter(5, OracleTypes.CURSOR);
					stmt.execute();
					ResultSet rs = (ResultSet)stmt.getObject(5);
					while (rs.next()) {
						ListaPrecioDTO lpDTO = new ListaPrecioDTO();
						lpDTO.setId_lista_precio(rs.getString(1));
						lpDTO.setId_producto(rs.getString(2));
						lpDTO.setServicio(rs.getString(3));
						lpDTO.setFec_ingreso_vigencia(rs.getString(4));
						lpDTO.setNum_precio(rs.getString(5));
						lpDTO.setCodigo(rs.getString(6));
						listLP.add(lpDTO);
					}
					rs.close();
					stmt.close();
				} catch (SQLException ex) {  
					log.error("Error ServiciosDAO.getLPbyContrato SQLException: " +ex.getMessage());
					System.out.println(ex.getMessage());
				} finally {
					conn.close();
					conn = null;
				}
				return listLP;
			}		
			
			public List<ListaPrecioDTO> setLPupdate(listaPrecioVO lpVO) throws SQLException, IOException{
				Connection conn = ConexionPool.getConnectionPool();			    
				List<ListaPrecioDTO> listLP = new ArrayList<ListaPrecioDTO>();
				try {
					String query = "{ call "+pkgVar+".Pkg_rec_servicios.setUpIns(?,?,?,?,?,?,?) }"; 	       
					CallableStatement  stmt = conn.prepareCall(query);
					stmt.setString(1, lpVO.getIdLP());
					stmt.setString(2, lpVO.getIdContrato());
					stmt.setString(3, lpVO.getIdProducto());
					stmt.setString(4, lpVO.getPrecio());
					stmt.setString(5, lpVO.getIduser());
					stmt.setString(6, lpVO.getTipo());
					stmt.registerOutParameter(7, OracleTypes.CURSOR);
					stmt.execute();
					ResultSet rs = (ResultSet)stmt.getObject(7);
					while (rs.next()) {
						ListaPrecioDTO lpDTO = new ListaPrecioDTO();
						lpDTO.setId_lista_precio(rs.getString(1));
						lpDTO.setServicio(rs.getString(2));
						lpDTO.setId_contrato(rs.getString(3));
						lpDTO.setNum_precio(rs.getString(4));
						listLP.add(lpDTO);
						if (!rs.getString(1).equals("0")) {
							System.out.println(rs.getString(1)+"A: "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4));
						}
						
					}
					rs.close();
					stmt.close();
				} catch (SQLException ex) {  
					log.error("Error ListaPrecioDAO.setLPupdate SQLException: " +ex.getMessage());
					System.out.println(ex.getMessage());
				} finally {
					conn.close();
					conn = null;
				}
				return listLP;
			}			

			 public int insertDocDetalleCaja(DocDetVO docdetVO) throws SQLException, IOException{

					int idMovimiento = 0;
					Connection conn = ConexionPool.getConnectionPool();	 
							try {						
								String query = "{ call "+pkgVar+".Pkg_rec_servicios.setInsDocDet(?,?,?,?,?,?,?,?) }";
								CallableStatement  stmt = conn.prepareCall(query);
								stmt.setString(1, docdetVO.getIdDocDet());
								stmt.setString(2, docdetVO.getIdProducto());		                
								stmt.setString(3, docdetVO.getFolioVenta());
								stmt.setString(4, docdetVO.getPrecio());
								stmt.setString(5, docdetVO.getGratuidad());				
								stmt.setString(6, docdetVO.getCantidad());
								stmt.setString(7, docdetVO.getTotal());								
								stmt.setString(8, docdetVO.getTotalPagar());
								stmt.execute();
								stmt.close();
							} catch (SQLException ex) {  
								log.error("Error ServiciosDAO.insertDocDetalle SQLException: " +ex.getMessage());
								System.out.println(ex.getMessage());			
							} finally {
								conn.close();
								conn = null;
							}			
						return idMovimiento;
				}
			 
			 public int insertDocDetalleTH(DocDetVOTH docdetVO) throws SQLException, IOException{

					int idMovimiento = 0;
					Connection conn = ConexionPool.getConnectionPool();	 
							try {						
								String query = "{ call "+pkgVar+".Pkg_rec_servicios.setInsDocDet(?,?,?,?,?,?,?,?) }";
								CallableStatement  stmt = conn.prepareCall(query);
								stmt.setString(1, docdetVO.getIdDocDet());
								stmt.setString(2, docdetVO.getIdProducto());		                
								stmt.setString(3, docdetVO.getFolioVenta());
								stmt.setString(4, docdetVO.getPrecio());
								stmt.setString(5, docdetVO.getGratuidad());				
								stmt.setString(6, docdetVO.getCantidad());
								stmt.setString(7, docdetVO.getTotal());								
								stmt.setString(8, docdetVO.getTotalPagar());
								stmt.execute();
								stmt.close();
							} catch (SQLException ex) {  
								log.error("Error ServiciosDAO.insertDocDetalle SQLException: " +ex.getMessage());
								System.out.println(ex.getMessage());			
							} finally {
								conn.close();
								conn = null;
							}			
						return idMovimiento;
				}
			 
			 public int deleteDocDetalleCaja(DocDetVO docdetVO) throws SQLException, IOException{

					int idMovimiento = 0;
					Connection conn = ConexionPool.getConnectionPool();	  
							try {						
								String query = "{ call "+pkgVar+".Pkg_documento.setDelDocDet(?,?) }";

								CallableStatement  stmt = conn.prepareCall(query);
								stmt.setString(1, docdetVO.getIdDocDet());		                
								stmt.setString(2, docdetVO.getIdProducto());
								//stmt.registerOutParameter(8, OracleTypes.INTEGER);
								stmt.execute();
								//idMovimiento = docdetVO.getIdDocDet(); 
								//System.out.println("Borrar iddoc: "+docdetVO.getIdDocDet()+" idprod: "+docdetVO.getIdProducto());
								stmt.close();
							} catch (SQLException ex) {  
								log.error("Error ServiciosDAO.deleteDocDetalle SQLException: " +ex.getMessage());
								System.out.println(ex.getMessage());				
							} finally {
								conn.close();
								conn = null;
							}			
						return idMovimiento;
				}			
			 
			 public int deleteDocDetalleTH(DocDetVOTH docdetVO) throws SQLException, IOException{

					int idMovimiento = 0;
					Connection conn = ConexionPool.getConnectionPool();	  
							try {						
								String query = "{ call "+pkgVar+".Pkg_documento.setDelDocDet(?,?) }";

								CallableStatement  stmt = conn.prepareCall(query);
								stmt.setString(1, docdetVO.getIdDocDet());		                
								stmt.setString(2, docdetVO.getIdProducto());
								//stmt.registerOutParameter(8, OracleTypes.INTEGER);
								stmt.execute();
								//idMovimiento = docdetVO.getIdDocDet(); 
								//System.out.println("Borrar iddoc: "+docdetVO.getIdDocDet()+" idprod: "+docdetVO.getIdProducto());
								stmt.close();
							} catch (SQLException ex) {  
								log.error("Error ServiciosDAO.deleteDocDetalle SQLException: " +ex.getMessage());
								System.out.println(ex.getMessage());				
							} finally {
								conn.close();
								conn = null;
							}			
						return idMovimiento;
				}				 
			 
				public List<docDetDTO> getDetalle(String folio) throws SQLException, IOException{
					Connection conn = ConexionPool.getConnectionPool();	
					List<docDetDTO> listTP = new ArrayList<docDetDTO>();
					try {
						String query = "{ call "+pkgVar+".Pkg_rec_servicios.getDetalle(?,?) }"; 	       
						CallableStatement  stmt = conn.prepareCall(query);
						stmt.setString(1, folio);
						stmt.registerOutParameter(2, OracleTypes.CURSOR);
						stmt.execute();
						ResultSet rs = (ResultSet)stmt.getObject(2);
						while (rs.next()) {
							docDetDTO tpDTO = new docDetDTO();
							tpDTO.setIdDocDet(rs.getString(1));
							tpDTO.setCantidad(rs.getString(2));
							tpDTO.setCodigo(rs.getString(3));
							tpDTO.setNombre(rs.getString(4));
							tpDTO.setPrecio(rs.getString(5));
							tpDTO.setSubtotal(rs.getString(6));
							tpDTO.setDescto(rs.getString(7));
							tpDTO.setDonacion(rs.getString(8));
							tpDTO.setIdProducto(rs.getString(9));
							listTP.add(tpDTO);
						}
						rs.close();
						stmt.close();
					} catch (SQLException ex) {  
						log.error("Error ServiciosDAO.getDetalle SQLException: " +ex.getMessage());
						System.out.println("Error ServiciosDAO.getDetalle SQLException: "+ ex.getMessage());
					} finally {
						conn.close();
						conn = null;
					}
					return listTP;
				}			
    // METODOS DE INFORMES DE ARQUEO
				
				 public List<ArqueoMovimientosDTO> getMvtosByFechaPago(String fechaArqueo, String iDAtencion) throws SQLException, IOException{
					 Connection conn = ConexionPool.getConnectionPool();		    
						List<ArqueoMovimientosDTO> listMvtoArqueo = new ArrayList<ArqueoMovimientosDTO>();
						try {
							String query = "{ call "+pkgVar+".pkg_rec_servicios.getMvtosByFechaPago(?,?,?) }";
							CallableStatement  stmt = conn.prepareCall(query);
							stmt.setString(1, fechaArqueo);		
							stmt.setString(2, iDAtencion);		
							stmt.registerOutParameter(3, OracleTypes.CURSOR);
							stmt.execute();
							ResultSet rs = (ResultSet)stmt.getObject(3);
							while (rs.next()) {
								
								ArqueoMovimientosDTO arqueoDTO = new ArqueoMovimientosDTO();				
								arqueoDTO.setFolioVenta(rs.getString(1));
								arqueoDTO.setIdMovimiento(rs.getString(2));
								arqueoDTO.setNombrePac(rs.getString(3));
								arqueoDTO.setApellidoPatPac(rs.getString(4));
								arqueoDTO.setApellidoMatPac(rs.getString(5));
								arqueoDTO.setRutPac(rs.getString(6));
								arqueoDTO.setDvPac(rs.getString(7));
								arqueoDTO.setTotal(rs.getString(8));
								arqueoDTO.setDonacion(rs.getString(9));
								arqueoDTO.setPrestacion(rs.getString(10));			
								arqueoDTO.setFormaPago(rs.getString(11));
								arqueoDTO.setPercenGratuidad(rs.getString(12));				
								listMvtoArqueo.add(arqueoDTO);
							}
							rs.close();
							stmt.close();
						} catch (SQLException ex) {  
							log.error("Error ServiciosDAO.getMvtosByFechaPago SQLException: " +ex.getMessage());
							System.out.println("Error ServiciosDAO.getMvtosByFechaPago SQLException: "+ ex.getMessage());
						} finally {
							conn.close();
							conn = null;
						}
						return listMvtoArqueo;
					}		
				 public List<ArqueoPagosDTO> getPagosByFechaPago(String fechaArqueo, String iDAtencion) throws SQLException, IOException{
					 Connection conn = ConexionPool.getConnectionPool();	    
						List<ArqueoPagosDTO> listMvtoArqueo = new ArrayList<ArqueoPagosDTO>();
						try {
							String query = "{ call "+pkgVar+".pkg_rec_servicios.getPagosByFechaPago(?,?,?) }";
							int idPago = 0;
							//int idPagonew = 0;
							CallableStatement  stmt = conn.prepareCall(query);
							stmt.setString(1, fechaArqueo);		
							stmt.setString(2, iDAtencion);		
							stmt.registerOutParameter(3, OracleTypes.CURSOR);
							stmt.execute();
							ResultSet rs = (ResultSet)stmt.getObject(3);
							while (rs.next()) {
								ArqueoPagosDTO arqueoDTO = new ArqueoPagosDTO();
								if (idPago!=rs.getInt(1)) {
									
									arqueoDTO.setCantFolioVenta(rs.getString(5));
									arqueoDTO.setTotal(rs.getString(2));
									idPago=rs.getInt(1);
								}
								arqueoDTO.setIdPago(rs.getString(1));
								arqueoDTO.setMonto(rs.getString(4));
								arqueoDTO.setFormaPago(rs.getString(3));
								arqueoDTO.setUserPago(rs.getString(6));
								listMvtoArqueo.add(arqueoDTO);
							}
							rs.close();
							stmt.close();
						} catch (SQLException ex) {  
							log.error("Error ServiciosDAO.getPagosByFechaPago SQLException: " +ex.getMessage());
							System.out.println("Error ServiciosDAO.getPagosByFechaPago SQLException: "+ ex.getMessage());
						} finally {
							conn.close();
							conn = null;
						}
						return listMvtoArqueo;
					}
				 
					public List<TotalesItemDTO> getTotalesGralesByItem(String fechaArqueo,String iDAtencion) throws SQLException, IOException{
						 Connection conn = ConexionPool.getConnectionPool();	    
						List<TotalesItemDTO> listTotalesItem = new ArrayList<TotalesItemDTO>();
						try {                                            
							String query = "{ call "+pkgVar+".pkg_rec_servicios.getTotalesGralesByItem(?,?,?) }"; 	       
							CallableStatement  stmt = conn.prepareCall(query);
							stmt.setString(1, fechaArqueo);		
							stmt.setString(2, iDAtencion);		
							stmt.registerOutParameter(3, OracleTypes.CURSOR);
							stmt.execute();
							ResultSet rs = (ResultSet)stmt.getObject(3);
							while (rs.next()) {
								TotalesItemDTO totItem = new TotalesItemDTO();				
								totItem.setFpago(rs.getString(1));
								totItem.setContrato(rs.getString(2));
								totItem.setIdContrato(rs.getString(3));
								totItem.setSumatoria(rs.getString(4));
								totItem.setCantidad(rs.getString(5));
								listTotalesItem.add(totItem);
							}
							rs.close();
							stmt.close();
						} catch (SQLException ex) {  
							log.error("Error ServiciosDAO.getTotalesGralesByItem SQLException: " +ex.getMessage());
							System.out.println("Error ServiciosDAO.getTotalesGralesByItem SQLException: "+ ex.getMessage());
						} finally {
							conn.close();
							conn = null;
						}
						return listTotalesItem;
					}		
					
					public List<AreaDTO> setCierreDiario(String fechaArqueo,String iDAtencion, String idUser, String idSede) throws SQLException, IOException{
						Connection conn = ConexionPool.getConnectionPool();
						String resultadoIns = "N";
						String descr = "";
						List<AreaDTO> listCierre = new ArrayList<AreaDTO>();
						try {                                            
							String query = "{ call "+pkgVar+".Pkg_rec_servicios.setCierreDiario(?,?,?,?,?) }";
							CallableStatement  stmt = conn.prepareCall(query);
							stmt.setString(1, fechaArqueo);
							stmt.setString(2, iDAtencion);
							stmt.setString(3, idUser);
							stmt.setString(4, idSede);
							stmt.registerOutParameter(5, OracleTypes.CURSOR);
							stmt.execute();
							ResultSet rs = (ResultSet)stmt.getObject(5);
							while (rs.next()) {
								AreaDTO totItem = new AreaDTO();				
								totItem.setIdArea(rs.getString(1));
								totItem.setDescripcion(rs.getString(2));
								listCierre.add(totItem);
								resultadoIns=rs.getString(1);
								descr=rs.getString(2);
							}
							rs.close();
							stmt.close();
							
						} catch (SQLException ex) {  
							log.error("Error ServiciosDAO.setCierreDiario SQLException: " +ex.getMessage());
							System.out.println("Error ServiciosDAO.setCierreDiario SQLException: "+ ex.getMessage());
						} finally {
							conn.close();
							conn = null;

						}
						//System.out.println("Cierre Diario con resultado:" +resultadoIns);
						return listCierre;
					}	
					
					public List<docEncDTO> getFVbyidPago(String idPago) throws SQLException, IOException{
						Connection conn = ConexionPool.getConnectionPool();
						List<docEncDTO> listCierre = new ArrayList<docEncDTO>();
						try {                                            
							String query = "{ call "+pkgVar+".Pkg_rec_servicios.getFVbyidPago(?,?) }";
							CallableStatement  stmt = conn.prepareCall(query);
							stmt.setString(1, idPago);
							stmt.registerOutParameter(2, OracleTypes.CURSOR);
							stmt.execute();
							ResultSet rs = (ResultSet)stmt.getObject(2);
							while (rs.next()) {
								docEncDTO totItem = new docEncDTO();				
								totItem.setIdPaciente(rs.getString(1));
								totItem.setIdContrato(rs.getString(2));
								totItem.setFolioVenta(rs.getString(3));
								totItem.setIdFormaPago(rs.getString(4));
								listCierre.add(totItem);
							}
							rs.close();
							stmt.close();
							
						} catch (SQLException ex) {  
							log.error("Error ServiciosDAO.getFVbyidPago SQLException: " +ex.getMessage());
							System.out.println("Error ServiciosDAO.getFVbyidPago SQLException: "+ ex.getMessage());
						}finally {
							conn.close();
							conn = null;
						}
						return listCierre;
					}					
					 public List<pagoDetVO> getDetalleByidPago(String idPago) throws SQLException, IOException{
						 Connection conn = ConexionPool.getConnectionPool();	    
							List<pagoDetVO> listMvtoArqueo = new ArrayList<pagoDetVO>();
							try {
								String query = "{ call "+pkgVar+".Pkg_rec_servicios.getDetalleByidPago(?,?) }";
								CallableStatement  stmt = conn.prepareCall(query);
								stmt.setString(1, idPago);		
								stmt.registerOutParameter(2, OracleTypes.CURSOR);
								stmt.execute();
								ResultSet rs = (ResultSet)stmt.getObject(2);
								while (rs.next()) {
									pagoDetVO arqueoDTO = new pagoDetVO();				
									arqueoDTO.setIdPagoDet(rs.getString(1));
									arqueoDTO.setIdContrato(rs.getString(2));
									arqueoDTO.setIdPago(rs.getString(3));
									arqueoDTO.setIdCheque(rs.getString(4));
									arqueoDTO.setIdFormaPago(rs.getString(5));
									arqueoDTO.setFormaPago(rs.getString(6));
									arqueoDTO.setMonto(rs.getString(7));
									arqueoDTO.setIdBanco(rs.getString(8));
									arqueoDTO.setBanco(rs.getString(9));
									arqueoDTO.setFecVenc(rs.getString(10));
									arqueoDTO.setFecVenc2(rs.getString(11));
									arqueoDTO.setSerieChq(rs.getString(12));
									arqueoDTO.setRutGirador(rs.getString(13));
									arqueoDTO.setIdTipoContrato(rs.getString(14));
									arqueoDTO.setRutContrato(rs.getString(15));
									arqueoDTO.setContrato(rs.getString(16));
									listMvtoArqueo.add(arqueoDTO);
								}
								rs.close();
								stmt.close();
							} catch (SQLException ex) {  
								log.error("Error ServiciosDAO.getDetalleByidPago SQLException: " +ex.getMessage());
								System.out.println("Error ServiciosDAO.getDetalleByidPago SQLException: "+ ex.getMessage());
							} finally {
								conn.close();
								conn = null;
							}
							return listMvtoArqueo;
						}
						public  List<GiradorDTO> getValidaProtestoRut(String rutGirador, String rutPaciente) throws SQLException, IOException{
							Connection conn = ConexionPool.getConnectionPool();		    
							List<GiradorDTO> listGirador = new ArrayList<GiradorDTO>();
							try {
								String query = "{ call "+pkgVar+".Pkg_rec_servicios.getValidaProtestoRut(?,?,?) }"; 	       
								CallableStatement  stmt = conn.prepareCall(query);
								stmt.setString(1, rutGirador);	
								stmt.setString(2, rutPaciente);		
								stmt.registerOutParameter(3, OracleTypes.CURSOR);
								stmt.execute();
								ResultSet rs = (ResultSet)stmt.getObject(3);
								if (rs.next()) {
									GiradorDTO girador = new GiradorDTO();
									girador.setMensajeProtesto(rs.getString(1));
									girador.setOrigenPACoGirador("S");
									listGirador.add(girador);
								}
								rs.close();
								stmt.close();
							} catch (SQLException ex) {  
								log.error("Error ServiciosDAO.getValidaProtestoRut SQLException: " +ex.getMessage());
								System.out.println("Error ServiciosDAO.getValidaProtestoRut SQLException: "+ ex.getMessage());
							} finally {
								conn.close();
								conn = null;
							}
							return listGirador;	
						}				
						public List<ArqueoMovimientosDTO> getControlCierreDiario(String fechaCierre, String idArea, String idSede, String tipoCierre) throws SQLException, IOException{
							Connection conn = ConexionPool.getConnectionPool();
							List<ArqueoMovimientosDTO> listResultado = new ArrayList<ArqueoMovimientosDTO>();
							try {                                            
								String query = "{ call "+pkgVar+".pkg_rec_servicios.getControlCierreDiario(?,?,?,?,?) }"; 	       
								CallableStatement  stmt = conn.prepareCall(query);
								stmt.setString(1, fechaCierre);		
								stmt.setString(2, idArea);
								stmt.setString(3, idSede);	
								stmt.setString(4, tipoCierre);
								
								stmt.registerOutParameter(5, OracleTypes.CURSOR);
								stmt.execute();			
								ResultSet rs = (ResultSet)stmt.getObject(5);
								while (rs.next()) {
									ArqueoMovimientosDTO arqueoDTO = new ArqueoMovimientosDTO();	
									arqueoDTO.setIdMovimiento(rs.getString(1));
									arqueoDTO.setNombrePac(rs.getString(2));
									listResultado.add(arqueoDTO);
								}
								rs.close();
								stmt.close();
							} catch (SQLException ex) {  
								log.error(" Error ArqueoDAO.getControlCierreDiario SQLException: " +ex.getMessage());
								System.out.println(ex.getMessage());
							} finally {
								conn.close();
								conn = null;
							}
							return listResultado;		
						}			
						
	public List  getGratPacByServ(String rutPac, String folio) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();		    
		List<GratuidadDetDTO> listResultado = new ArrayList<GratuidadDetDTO>();
		try {
			String query = "{ call "+pkgVar+".Pkg_rec_servicios.getGratPacByServ(?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, rutPac);	
			stmt.setString(2, folio);		
			stmt.registerOutParameter(3, OracleTypes.CURSOR);
			stmt.execute();			
			ResultSet rs = (ResultSet)stmt.getObject(3);
			while (rs.next()) {
				GratuidadDetDTO listaGrat = new GratuidadDetDTO();	
				listaGrat.setRetorno(rs.getString(1));
				if(Integer.parseInt(rs.getString(1)) > 0){		
					listaGrat.setPercenLiga(rs.getString(2));
				}
				//listaGrat.setPrecioPAC(rs.getString(3));
				//listaGrat.setPrecioLiga(Integer.toString((rs.getInt(5)-rs.getInt(3))));
				listResultado.add(listaGrat);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error ServiciosDAO.getGratPacByServ SQLException: " +ex.getMessage());
			System.out.println("Error ServiciosDAO.getGratPacByServ SQLException: "+ ex.getMessage());
		} finally {
			conn.close();
			conn = null;
		}
		return listResultado;	
	}								
	
	public List<ServicioDTO> getServicio(String idServicio) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();	
		List<ServicioDTO> listTP = new ArrayList<ServicioDTO>();
		try {
			String query = "{ call "+pkgVar+".Pkg_rec_servicios.getServicio(?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, idServicio);
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);
			while (rs.next()) {
				ServicioDTO tpDTO = new ServicioDTO();
				tpDTO.setIdServicioTipo(rs.getString(1));
				tpDTO.setStr_descripcion(rs.getString(2));
				tpDTO.setStr_codigo(rs.getString(3));
				listTP.add(tpDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error ServiciosDAO.getServicios SQLException: " +ex.getMessage());
			System.out.println("Error ServiciosDAO.getServicios SQLException: "+ ex.getMessage());
		} finally {
			conn.close();
			conn = null;
		}
		return listTP;
	}				
	
}
